package com.lichongbing.lyoggl.mapper;

import com.alibaba.fastjson.JSONObject;
import com.lichongbing.lyoggl.model.Money;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lichongbing.lyoggl.model.ReportMounth;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @Entity com.lichongbing.lyoggl.model.Money
 */
@Component
public interface MoneyMapper extends BaseMapper<Money> {
    /**
     * 查询明细
     *
     * @param userid
     * @return
     */
    @Select("select * from money where uid = #{userid} order by date_time desc")
    List<Money> findByUid(String userid);

    /**
     * 删除明细
     *
     * @param id
     * @param userid
     * @return
     */
    @Delete("delete from money where id = #{id} and uid = #{userid}")
    int deleteByUid(String id, String userid);

    /**
     * 获取月报
     *
     * @param mouth
     * @param userid
     * @return
     */
    @Select("select distinct tt.* from (select\n" +
            "                               (select sum(money) from money where uid = #{userid} and type = '1' and mouth= #{mouth} and t.year = #{year}) as money1,\n" +
            "                               (select sum(money) from money where uid = #{userid} and type = '0' and mouth= #{mouth} and t.year = #{year}) as money0,\n" +
            "                               (select sum(money1) from account a where uid = #{userid}  and mouth= #{mouth} and t.year = #{year}) as pay1,\n" +
            "                               (select sum(money3+pay+pay1) from account where uid = #{userid} and mouth= #{mouth} and t.year = #{year}) as pay2,\n" +
            "                               (select sum(balance) from bank where uid = #{userid} and type in (0,2) ) as duting,\n" +
            "                               (select sum(balance) from bank where uid = #{userid} and type= 1 ) as balance,\n" +
            "                               (select sum(duted) from bank where uid = #{userid} and type in (0,2) ) as duted\n" +
            "                           from money t) as tt")
    ReportMounth reportMounth(String mouth, String year, String userid);

    /**
     * 收入
     * @param mouth
     * @param year
     * @param userid
     * @return
     */
    @Select("select sum(money) from money where uid = #{userid} and type = '1' and mouth= #{mouth} and year = #{year}")
    String getMoney0(String mouth, String year, String userid);

    /**
     * 支出
     * @param mouth
     * @param year
     * @param userid
     * @return
     */
    @Select("select sum(money) from money where uid = #{userid} and type = '0' and mouth= #{mouth} and year = #{year}")
    String getMoney1(String mouth, String year, String userid);

    /**
     * 最低还款
     * @param mouth
     * @param year
     * @param userid
     * @return
     */
    @Select("select sum(money1) from account  where uid = #{userid}  and mouth= #{mouth} and year = #{year}")
    String getPay1(String mouth, String year, String userid);

    /**
     * 利息杂费
     * @param mouth
     * @param year
     * @param userid
     * @return
     */
    @Select("select sum(money3+pay+pay1) from account where uid = #{userid} and mouth= #{mouth} and year = #{year}")
    String getPay2(String mouth, String year, String userid);

    /**
     * 信用额度
     * @param userid
     * @return
     */
    @Select("select sum(balance) from bank where uid = #{userid} and type in (0,2)")
    String getDuting(String userid);
    /**
     * 欠款额度
     * @param userid
     * @return
     */
    @Select("select sum(duted) from bank where uid = #{userid} and type in (0,2)")
    String getDuted(String userid);

    /**
     * 现金额度
     * @param userid
     * @return
     */
    @Select("select sum(balance) from bank where uid = #{userid} and type = 1 ")
    String getBalance(String userid);

    /**
     * 每日支出汇总
     * @param day
     * @param year
     * @param mouth
     * @return
     */
    @Select("select sum(money) from money where uid = #{userid} and type = '0' and mouth =  #{mouth} and year =  #{year} and day =  #{day}")
    String reportDay(String day, String year, String mouth,String userid);
}
